/**
 * Migration: Create Loyalty Tables and Columns
 * - points_history table
 * - add is_cash_voucher column to menu_items (if missing)
 */

module.exports = {
  async up(connection, dbType) {
    // Create points_history table
    const createPointsHistoryTable = dbType === 'sqlite'
      ? `CREATE TABLE IF NOT EXISTS points_history (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER NOT NULL,
          points_change INTEGER NOT NULL,
          type TEXT NOT NULL CHECK (type IN ('earn','spend','adjust','expire','reverse')),
          reason TEXT,
          source TEXT,
          reference_id TEXT,
          order_id INTEGER,
          status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','expired','reversed','pending')),
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          expires_at DATETIME,
          FOREIGN KEY (user_id) REFERENCES users(user_id)
        )`
      : `CREATE TABLE IF NOT EXISTS points_history (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          user_id BIGINT NOT NULL,
          points_change INT NOT NULL,
          type ENUM('earn','spend','adjust','expire','reverse') NOT NULL,
          reason VARCHAR(255),
          source VARCHAR(64),
          reference_id VARCHAR(100),
          order_id BIGINT,
          status ENUM('active','expired','reversed','pending') NOT NULL DEFAULT 'active',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          expires_at TIMESTAMP NULL,
          INDEX idx_user_created (user_id, created_at),
          INDEX idx_user_expires (user_id, expires_at),
          UNIQUE KEY uniq_user_source_ref (user_id, source, reference_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

    if (dbType === 'sqlite') {
      await connection.run(createPointsHistoryTable);
    } else {
      await connection.execute(createPointsHistoryTable);
    }

    // Add is_cash_voucher column to menu_items if missing
    if (dbType === 'sqlite') {
      // Check column existence
      const pragma = await connection.all(`PRAGMA table_info('menu_items')`);
      const hasCol = Array.isArray(pragma) && pragma.some(col => String(col.name) === 'is_cash_voucher');
      if (!hasCol) {
        await connection.run(`ALTER TABLE menu_items ADD COLUMN is_cash_voucher INTEGER DEFAULT 0`);
      }
    } else {
      // MySQL: check information_schema
      const [rows] = await connection.execute(`SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'menu_items' AND column_name = 'is_cash_voucher'`);
      if (!rows || rows.length === 0) {
        await connection.execute(`ALTER TABLE menu_items ADD COLUMN is_cash_voucher BOOLEAN DEFAULT FALSE`);
        await connection.execute(`ALTER TABLE menu_items ADD INDEX idx_is_cash_voucher (is_cash_voucher)`);
      }
    }

    console.log('✅ Created points_history and ensured menu_items.is_cash_voucher');
  },

  async down(connection, dbType) {
    // Drop points_history table
    const dropPointsHistory = `DROP TABLE IF EXISTS points_history`;
    if (dbType === 'sqlite') {
      await connection.run(dropPointsHistory);
    } else {
      await connection.execute(dropPointsHistory);
    }

    // Optional: do NOT drop column is_cash_voucher to avoid data loss
    console.log('✅ Dropped points_history (kept menu_items.is_cash_voucher)');
  }
};